﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Linq;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;

namespace AWS.SLAM.Hierarchy.Data
{
	internal class DataService
	{
		#region Private State

		private DataContext _dataContext;
		private static List<string> _verifiedTableList = new List<string>();
		private static bool _databaseCreated;
		private static string _dataSchema;

		#endregion

		#region Constructors and Destructors

		public DataService(string connectionString, string dataSchema)
		{
			_dataContext = new DataContext(connectionString);
			_dataSchema = dataSchema;
		}

		~DataService()
		{
			_dataContext.Dispose();
		}

		#endregion

		#region Database Creation

		public void AddHierarchyColumns(string typeName)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.AddHierarchyColumns, _dataSchema, GetTableName(typeName)));
		}

		public void EnsureDatabaseExists()
		{
			if (!_databaseCreated && !_dataContext.DatabaseExists())
			{
				throw new DataException("Database does not exist.");
				//_dataContext.CreateDatabase();
			}

			_databaseCreated = true;
		}

		public bool VerifyHierarchyColumnsExist(string typeName)
		{
			EnsureDatabaseExists();
			string tableName = GetTableName(typeName);

			bool exists = false;
			if (_verifiedTableList.Contains(tableName))
			{
				exists = true;
			}
			else
			{
				exists = _dataContext.ExecuteQuery<int>(String.Format(Queries.VerifyHierarchyColumnsExist, _dataSchema, tableName)).SingleOrDefault() > 0;
				if (exists)
				{
					_verifiedTableList.Add(tableName);
				}
			}
			return exists;
		}

		private string GetTableName(string typeName)
		{
			return Regex.Replace(typeName, "[\\W\\s]", "");
		}

		#endregion

		#region Data Management

		public void AddAsRootNode(string typeName, string idColumnName, string id)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.SetAsRootNode, _dataSchema, GetTableName(typeName), id, idColumnName));
		}

		public void AddAsLastChildNode(string typeName, string idColumnName, string parentId, string id)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.SetAsLastChildNode, _dataSchema, GetTableName(typeName), parentId, id, idColumnName));
		}

		public void AddAsLeftSiblingNode(string typeName, string idColumnName, string siblingId, string id)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.SetAsLeftSiblingNode, _dataSchema, GetTableName(typeName), siblingId, id, idColumnName));
		}

		public int MoveToRootNode(string typeName, string idColumnName, string idColumnType, string id)
		{
			return _dataContext.ExecuteCommand(String.Format(Queries.MoveNodeToRootNode, _dataSchema, GetTableName(typeName), id, idColumnName, idColumnType));
		}

		public int MoveToLastChildNode(string typeName, string idColumnName, string idColumnType, string parentId, string id)
		{
			return _dataContext.ExecuteCommand(String.Format(Queries.MoveNodeToLastChildNode, _dataSchema, GetTableName(typeName), parentId, id, idColumnName, idColumnType));
		}

		public int MoveToLeftSiblingNode(string typeName, string idColumnName, string idColumnType, string siblingId, string id)
		{
			return _dataContext.ExecuteCommand(String.Format(Queries.MoveNodeToLeftSiblingNode, _dataSchema, GetTableName(typeName), siblingId, id, idColumnName, idColumnType));
		}

		public void DeleteNode(string typeName, string idColumnName, string id)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.DeleteHierarchyBranch, _dataSchema, GetTableName(typeName), id, idColumnName));
		}

		public void RecalculateHierarchyLevels(string typeName, string idColumnName)
		{
			_dataContext.ExecuteCommand(String.Format(Queries.RecalculateHierarchyLevels, _dataSchema, GetTableName(typeName), idColumnName));
		}

		#endregion
	}
}
